# %%
# 初始化
import reader
from common import *

(year, month), _ = year_month()

FP_GONGDAN_DANGYUE = (
    f"data/0-工单/家客工单导出_潮州_{year}-{month:02}.zip"
)

# %%
# 读取工单
usecols = (
    "工单号",
    "CRM工单号",
    "CRM业务流水号",
    "地市",
    "派单人",
    "操作类型",
    "是否施工标识",
    "宽带帐号",
    "派单日期",
    "结单时间",
    "工单状态",
    "装维组/用户班",
    "装维人员登录账号",
    "客户类型",
    "综合网格",
    "用户实际速率",
    "速率测试是否达标",
    "是否派发给装维",
    "速率测试达标情况",
    "五级地址ID",
    "更换ONU是否成功",
)
df = reader.read_gongdan(FP_GONGDAN_DANGYUE, usecols)

# %%
# 工单筛选
# 操作类型 = 宽带速率变更
# 是否施工 = 普通开通施工
# 是否派发给装维 = 是
# 工单状态 =  归档
mask = (
    (df["操作类型"] == "宽带速率变更")
    & (df["是否施工标识"] == "普通开通施工")
    & (df["是否派发给装维"] == "是")
    & (df["工单状态"] == "归档")
)
df = df[mask]

# %%
# 提速判定
df["提速判定"] = "非千兆"
df.loc[df.用户实际速率 == "1000M", "提速判定"] = "千兆"

# %%
# 是否更换onu
df["是否更换onu"] = S_BYPASSED
df.loc[(df.提速判定 == "非千兆"), "是否更换onu"] = S_NOT_PASSED
df.loc[
    (df.提速判定 == "非千兆") & (df.更换ONU是否成功 == S_YES), "是否更换onu"
] = S_PASSED

# %%
# 是否速率达标
df["是否速率达标"] = S_NOT_PASSED
df.loc[
    df.速率测试达标情况.isin(["已达标", "整改后达标"]), "是否速率达标"
] = S_PASSED


# %%
# 是否重复派单
df["是否重复派单"] = S_NOT_PASSED
tmp = df.groupby("宽带帐号").filter(lambda x: len(x) > 1)
df.loc[~df.宽带帐号.isin(tmp.宽带帐号), "是否重复派单"] = S_PASSED

tmp["_order_"] = 0
tmp.loc[tmp.速率测试达标情况 == "整改后达标", "_order_"] = 1
tmp.loc[tmp.速率测试达标情况 == "已达标", "_order_"] = 2

tmp.sort_values(
    by=["宽带帐号", "_order_", "派单时间"], inplace=True
)
tmp.drop_duplicates(
    subset=["宽带帐号"], keep="last", inplace=True
)
df.loc[tmp.index, "是否重复派单"] = S_PASSED

# %%
# 地市稽核结果
# 千兆： “是否速率达标”、“超长工单核查” 均通过
# 非千兆：“是否更换onu”、“是否速率达标”、“超长工单核查” 均通过
df["地市稽核结果"] = S_NOT_PASSED
mask_1000m = (df.用户实际速率 == "1000M") & df[
    ["是否速率达标", "是否重复派单"]
].isin([S_BYPASSED, S_PASSED]).all(axis=1)
df.loc[mask_1000m, "地市稽核结果"] = S_PASSED
mask_lt_1000m = (df.用户实际速率 != "1000M") & df[
    ["是否更换onu", "是否速率达标", "是否重复派单"]
].isin([S_BYPASSED, S_PASSED]).all(axis=1)
df.loc[mask_lt_1000m, "地市稽核结果"] = S_PASSED

# %%
# 是否结算
df["是否结算"] = S_NO
df.loc[df["地市稽核结果"] == S_PASSED, "是否结算"] = S_YES

# %%
# 完成
df.to_excel(
    f"report/6-提速/{year}{month:02}_提速工单稽核明细表.xlsx",
    index=False,
)

# %%
